﻿using ClosedXML.Excel;
using CsvHelper;
using CsvHelper.Configuration;
using TradeDataAPI.Core.BaseProvider;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Threading.Tasks;

namespace TradeDataAPI.Core.Common
{
    public class ExcelHerpel
    {
        private readonly static int DaysToKeep = 7;

        public static void ClearTask()
        {

            ClearExcel();//清理7天前的excel文件
            ClearLog();//删除日志
            ClearExcelPort();//清理7天前导出文件

        }





        /// <summary>
        /// 清理7天前的excel文件
        /// </summary>
        public static void ClearExcel()
        {
            //如果是7天前的文件夹 直接全部删除
            string uploadFolderPath = Path.Combine(Directory.GetCurrentDirectory(), "Upload");
            DateTime currentDate = DateTime.Now;

            if (Directory.Exists(uploadFolderPath))
            {
                foreach (var directory in Directory.GetDirectories(uploadFolderPath))
                {
                    string folderName = Path.GetFileName(directory);
                    if (DateTime.TryParseExact(folderName, "yyyyMMdd", null, DateTimeStyles.None, out DateTime folderDate))
                    {
                        if ((currentDate - folderDate).TotalDays > DaysToKeep)
                        {
                            Directory.Delete(directory, true);
                            Console.WriteLine($"7天前的【Excel文件】已经删除: {directory}");
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 删除日志
        /// </summary>
        public static void ClearLog()
        {
            string quartzErrorFolderPath = Path.Combine(Directory.GetCurrentDirectory(), "quartz", "error");
            DateTime currentDate = DateTime.Now;
            // 删除 quartz/error 文件夹中日期大于等于7天的文件
            if (Directory.Exists(quartzErrorFolderPath))
            {
                foreach (var file in Directory.GetFiles(quartzErrorFolderPath))
                {
                    string fileName = Path.GetFileNameWithoutExtension(file);
                    if (DateTime.TryParseExact(fileName, "yyyy-MM-dd", null, DateTimeStyles.None, out DateTime fileDate))
                    {
                        if ((currentDate - fileDate).TotalDays > DaysToKeep)
                        {
                            File.Delete(file);
                            Console.WriteLine($"7天前的【ErrorLog文件】已经删除: {file}");
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 删除导出文件
        /// </summary>
        public static void ClearExcelPort()
        {
            string FolderPath = Path.Combine(Directory.GetCurrentDirectory(), "Download", "ExcelExport");
            DateTime currentDate = DateTime.Now;
            if (Directory.Exists(FolderPath))
            {
                foreach (var directory in Directory.GetDirectories(FolderPath))
                {
                    string folderName = Path.GetFileName(directory);
                    if (DateTime.TryParseExact(folderName, "yyyyMMdd", null, DateTimeStyles.None, out DateTime folderDate))
                    {
                        if ((currentDate - folderDate).TotalDays > DaysToKeep)
                        {
                            Directory.Delete(directory, true);
                            Console.WriteLine($"7天前的【导出文件】已经删除: {directory}");
                        }
                    }
                }
            }


        }


        /// <summary>
        /// 删除原文件中无关紧要的部分信息
        /// </summary>
        /// <param name="inputPath">原文件地址</param>
        /// <param name="outputPath">保存的文件地址</param>
        public static void RemoveColumnsContainingSlash(string inputPath, string outputPath)
        {
            try
            {
                if (!File.Exists(inputPath))
                {
                    return;
                }

                using (var workbook = new XLWorkbook(inputPath))
                {
                    Console.WriteLine("File read successfully.");
                    var worksheet = workbook.Worksheet(1); // 获取第一个工作表
                    var range = worksheet.RangeUsed();
                    int rowCount = range.RowCount();
                    int colCount = range.ColumnCount();


                    // 找到包含 "/" 的列索引
                    var columnsToDelete = new HashSet<int>();
                    for (int col = 1; col <= colCount; col++)
                    {
                        for (int row = 1; row <= rowCount; row++)
                        {
                            var cell = worksheet.Cell(row, col);
                            var cellValue = cell.GetValue<string>();
                            if (!string.IsNullOrWhiteSpace(cellValue) && (cellValue.Contains("/") || cellValue.Contains("路线信息") || cellValue.Contains("排班信息") || cellValue.Contains("厂区信息") || cellValue.Contains("启用状态")))
                            {
                                columnsToDelete.Add(col);
                                break;
                            }
                        }
                    }

                    // 删除包含 "/" 的列
                    foreach (var colIndex in columnsToDelete.OrderByDescending(c => c))
                    {
                        try
                        {
                            worksheet.Column(colIndex).Delete();
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message + ex.StackTrace);
                        }
                    }

                    workbook.SaveAs(outputPath); // 保存修改后的文件
                    Console.WriteLine("File saved successfully.");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
                Console.WriteLine(ex.StackTrace);
            }
        }


        /// <summary>
        /// 读取Excel文件到动态列表
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static (List<dynamic> dataList, List<dynamic> RouteInfo, List<dynamic> NumberWorks) ReadExcelToDynamicList(string filePath)
        {
            var dataList = new List<dynamic>();
            var dataList2 = new List<dynamic>();
            var NumberWorks = new List<dynamic>();
            try
            {
                Console.WriteLine($"Reading file: {filePath}");
                if (!File.Exists(filePath))
                {
                    return (dataList, dataList2, NumberWorks);
                }

                using (var workbook = new XLWorkbook(filePath))
                {
                    Console.WriteLine("File read successfully.");
                    var worksheet = workbook.Worksheet(1); // 获取第一个工作表
                    var range = worksheet.RangeUsed();
                    int rowCount = range.RowCount();
                    int colCount = range.ColumnCount();

                    // 读取列名称并排除空列
                    var columnNames = new List<string>();
                    var nonEmptyColumns = new List<int>();
                    for (int col = 1; col <= colCount; col++)
                    {
                        var columnName = worksheet.Cell(1, col).GetValue<string>();
                        if (!string.IsNullOrWhiteSpace(columnName))
                        {
                            columnNames.Add(columnName);
                            nonEmptyColumns.Add(col);
                        }
                    }

                    // 找到“路线信息”和“工号”列的索引
                    int routeInfoIndex = -1;
                    int employeeIdIndex = -1;
                    int factoryIdIndex = -1;
                    int TemNumberWorkIndex = -1;
                    int departmentIdIndex = -1;
                    for (int col = 1; col <= nonEmptyColumns.Count; col++)
                    {
                        var columnName = worksheet.Cell(1, nonEmptyColumns[col - 1]).GetValue<string>();
                        if (columnName == "路线信息")
                        {
                            routeInfoIndex = nonEmptyColumns[col - 1];
                        }
                        else if (columnName == "工号")
                        {
                            employeeIdIndex = nonEmptyColumns[col - 1];
                        }
                        else if (columnName == "厂区信息")
                        {
                            factoryIdIndex = nonEmptyColumns[col - 1];
                        }
                        else if (columnName == "临时工号")
                        {
                            TemNumberWorkIndex = nonEmptyColumns[col - 1];
                        }
                        else if (columnName == "部门")
                        {
                            departmentIdIndex = nonEmptyColumns[col - 1];
                        }
                    }

                    // 读取每一行的数据并组装成对象
                    for (int row = 2; row <= rowCount; row++) // 从第二行开始读取数据，第一行是列名称
                    {
                        bool isEmptyRow = true;
                        foreach (var col in nonEmptyColumns)
                        {
                            if (!string.IsNullOrWhiteSpace(worksheet.Cell(row, col).GetValue<string>()))
                            {
                                isEmptyRow = false;
                                break;
                            }
                        }

                        if (isEmptyRow)
                        {
                            continue;
                        }

                        dynamic data = new ExpandoObject();
                        var dataDict = (IDictionary<string, object>)data;

                        dynamic data2 = new ExpandoObject();
                        var dataDict2 = (IDictionary<string, object>)data2;



                        dynamic data3 = new ExpandoObject();
                        var dataDict3 = (IDictionary<string, object>)data2;

                        foreach (var col in nonEmptyColumns)
                        {
                            var cellValue = worksheet.Cell(row, col).GetValue<string>();
                            dataDict[columnNames[nonEmptyColumns.IndexOf(col)]] = cellValue;
                        }

                        if (routeInfoIndex != -1)
                        {
                            dataDict2["路线信息"] = worksheet.Cell(row, routeInfoIndex).GetValue<string>() ?? "";
                        }
                        if (employeeIdIndex != -1)
                        {
                            dataDict3["工号"] = worksheet.Cell(row, employeeIdIndex).GetValue<string>() ?? "";
                            dataDict2["工号"] = worksheet.Cell(row, employeeIdIndex).GetValue<string>() ?? "";
                        }
                        if (factoryIdIndex != -1)
                        {
                            dataDict2["厂区信息"] = worksheet.Cell(row, factoryIdIndex).GetValue<string>() ?? "";
                        }
                        if (TemNumberWorkIndex != -1)
                        {
                            dataDict2["临时工号"] = worksheet.Cell(row, TemNumberWorkIndex).GetValue<string>() ?? "";
                        }
                        if (departmentIdIndex != -1)
                        {
                            dataDict2["部门"] = worksheet.Cell(row, departmentIdIndex).GetValue<string>() ?? "";
                        }

                        dataList.Add(data);
                        dataList2.Add(data2);
                        NumberWorks.Add(data3);
                    }
                }

                return (dataList, dataList2, NumberWorks);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
                if (ex.InnerException != null)
                {
                    Console.WriteLine($"Inner Exception: {ex.InnerException.Message}");
                }
                Console.WriteLine(ex.StackTrace);
                return (dataList, dataList2, NumberWorks);
            }
        }



        /// <summary>
        /// 读取csv文件
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static List<Dictionary<string, object>> ReadCsv(string filePath)
        {
            Console.WriteLine($"{DateTime.Now} - Reading CSV file: {filePath}");
            try
            {
                var result = new List<Dictionary<string, object>>();

                using (var reader = new StreamReader(filePath))
                using (var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture)))
                {
                    var records = csv.GetRecords<dynamic>();
                    foreach (var record in records)
                    {
                        var row = new Dictionary<string, object>();
                        foreach (var property in record)
                        {
                            row[property.Key] = property.Value;
                        }
                        result.Add(row);
                    }
                }
                Console.WriteLine($"{DateTime.Now} - Reading CSV file:Success");
                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"{DateTime.Now} - Reading CSV file:Error" + ex.Message + ex.StackTrace);
                return new List<Dictionary<string, object>>();
            }

        }

        public static async Task<List<Dictionary<string, object>>> ReadCsvFromHttp(string url)
        {
            Console.WriteLine($"{DateTime.Now} - Reading CSV file from URL: {url}=====================");
            try
            {
                var result = new List<Dictionary<string, object>>();
                using (var httpClient = new HttpClient())
                {
                    // 发送 HTTP 请求获取文件内容
                    var response = await httpClient.GetAsync(url);
                    // 确保请求成功
                    response.EnsureSuccessStatusCode();
                    // 读取响应内容为字符串
                    var csvContent = await response.Content.ReadAsStringAsync();

                    // 使用 StringReader 读取 CSV 内容
                    using (var reader = new StringReader(csvContent))
                    using (var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture)))
                    {
                        // 获取 CSV 记录
                        var records = csv.GetRecords<dynamic>();
                        foreach (var record in records)
                        {
                            var row = new Dictionary<string, object>();
                            foreach (var property in record)
                            {
                                row[property.Key] = property.Value;
                            }
                            result.Add(row);
                        }
                    }
                }
                Console.WriteLine($"{DateTime.Now} - Reading CSV file from URL: Success");
                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"{DateTime.Now} - Reading CSV file from URL: Error - {ex.Message} {ex.StackTrace}");
                return new List<Dictionary<string, object>>();
            }
        }










    }
}
